{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2717: DtypeWarning: Columns (91,98,104,121,125) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "#读取CAPSE/CTRIP数据\n",
    "dt_capse=pd.read_csv(r'C:\\Users\\xy\\Desktop\\201807CapseData.csv')\n",
    "dt_ctrip=pd.read_csv(r'C:\\Users\\xy\\Desktop\\201807CtripData.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#CAPSE的打分项过滤掉文本信息，只保留数字\n",
    "def clean_data(x):\n",
    "    for i in range(len(x)):\n",
    "        if re.match('[\\d]',str(x[i])) is not None:\n",
    "            x[i]=int(re.match(r'[\\d]',str(x[i])).group())\n",
    "    return x\n",
    "dt_capse.ix[:,23:]=dt_capse.ix[:,23:].apply(clean_data,axis=1)\n",
    "dt_capse.replace('CAPSE','',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#航司是否提供快速退改签服务、何时首次收到航班变更的信息量化赋分\n",
    "dt_capse['航司是否提供快速退改签服务？'].replace({'A':5,'B':1,'C':3},inplace=True)\n",
    "dt_capse['何时首次收到航班变更的信息？'].replace({'A':5,'B':4,'C':'3','D':1},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#整理CAPSE/CTRIP数据\n",
    "dt_capse=dt_capse[['航班号','航班日期','出发地编码','目的地编码','出发地','目的地','购票流程便捷性','购票服务规范性','空乘服务','机上广播',\n",
    "    '机上餐食','机上饮品','机上娱乐','客舱环境','座椅舒适度','机上卫生间','对行李保护','行李传输时间满意度（在行李转盘等待的时间）',\n",
    "    '出发机场摆渡车服务','到达机场摆渡车服务','登机满意度','机场柜台值机满意度','机场自助值机满意度','手机短信值机满意度',\n",
    "    '航司官网值机满意度','航司APP值机满意度','飞常准值机满意度','其他渠道值机满意度','何时首次收到航班变更的信息？',\n",
    "    '航司是否提供快速退改签服务？','延误餐满意度','住宿满意度','协助提供延误餐满意度','协助提供住宿满意度','地面不正常航班保障',\n",
    "    '机上不正常航班保障','评论']]\n",
    "dt_ctrip=dt_ctrip[['航班号','航班日期','出发地三字码','目的地三字码','出发地','目的地','航班服务得分(customscore1)','航空餐食得分(customscore2)','机上娱乐得分(customscore5)','航班座舱得分(customscore4)','用户评论(content)']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#CTRIP数据清洗\n",
    "dt_ctrip.columns=['航班号','航班日期','出发地编码', '目的地编码', '出发地', '目的地', '空乘服务', '机上餐食','机上娱乐', '座椅舒适度', '评论']\n",
    "dt_ctrip.replace('0',np.nan,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#合并CTRIP/CAPSE数据为dt\n",
    "dt=pd.concat([dt_capse,dt_ctrip]).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#转化为数据类型\n",
    "dt[['购票服务规范性','购票流程便捷性','机上餐食','机上饮品','客舱环境','座椅舒适度','机上卫生间','对行李保护',\n",
    "    '行李传输时间满意度（在行李转盘等待的时间）','出发机场摆渡车服务','到达机场摆渡车服务','登机满意度','机场柜台值机满意度','机场自助值机满意度',\n",
    "    '航司APP值机满意度','航司官网值机满意度','手机短信值机满意度','飞常准值机满意度','其他渠道值机满意度','住宿满意度','协助提供住宿满意度',\n",
    "    '延误餐满意度','协助提供延误餐满意度','机上不正常航班保障','地面不正常航班保障','何时首次收到航班变更的信息？','航司是否提供快速退改签服务？','空乘服务','机上娱乐','机上广播']]=dt[['购票服务规范性','购票流程便捷性','机上餐食','机上饮品','客舱环境','座椅舒适度','机上卫生间','对行李保护',\n",
    "    '行李传输时间满意度（在行李转盘等待的时间）','出发机场摆渡车服务','到达机场摆渡车服务','登机满意度','机场柜台值机满意度','机场自助值机满意度',\n",
    "    '航司APP值机满意度','航司官网值机满意度','手机短信值机满意度','飞常准值机满意度','其他渠道值机满意度','住宿满意度','协助提供住宿满意度',\n",
    "    '延误餐满意度','协助提供延误餐满意度','机上不正常航班保障','地面不正常航班保障','何时首次收到航班变更的信息？','航司是否提供快速退改签服务？','空乘服务','机上娱乐','机上广播']].apply(pd.to_numeric,errors='coerce')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df=dt[['航班号','航班日期','出发地编码','目的地编码','出发地','目的地','购票流程便捷性','购票服务规范性','空乘服务','机上广播','机上餐食','机上饮品','机上娱乐','客舱环境','座椅舒适度','机上卫生间','对行李保护','行李传输时间满意度（在行李转盘等待的时间）','出发机场摆渡车服务','到达机场摆渡车服务','登机满意度','机场柜台值机满意度','机场自助值机满意度','手机短信值机满意度','航司官网值机满意度','航司APP值机满意度','飞常准值机满意度','其他渠道值机满意度','何时首次收到航班变更的信息？','航司是否提供快速退改签服务？','延误餐满意度','协助提供延误餐满意度','住宿满意度','协助提供住宿满意度','地面不正常航班保障','机上不正常航班保障','评论']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#只保留ZH的点评\n",
    "df_index=[]\n",
    "for k in df['航班号']:\n",
    "    df_index.append(k[:2]=='ZH')\n",
    "df=df[df_index]\n",
    "df.reset_index(inplace=True)\n",
    "del df['index']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel\\__main__.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "#将航班日期含有时间的部分剔除，只保留日期部分\n",
    "def str10(x):\n",
    "    for i in range(len(x)):\n",
    "        x[i]=x[i][:10]\n",
    "    return x    \n",
    "df['航班日期']=str10(df['航班日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#将航班号和航班日期合并成yi'lei\n",
    "def num_time(x,y):\n",
    "    for i in range(len(x)):\n",
    "        x[i]=x[i]+'/'+y[i]\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel\\__main__.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "df['index']=num_time(df['航班号'],df['航班日期'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_=df.groupby('航班号').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_['评论']=''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel\\__main__.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "for m in range(len(df_.index)):\n",
    "    for n in range(len(df['航班号'])):\n",
    "        if df['航班号'][n]==df_.index[m] and df['航班号'][n]!=None:\n",
    "            df_['评论'][m]=str(df_['评论'][m])+str(df['评论'][n])+'/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df_.to_excel(r'C:\\Users\\xy\\Desktop\\201807DataForZH.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
